/**
 * *********************** ******* ***********************************
 *
 *  
 * 
 *  
 * *******************************************************************
 */

package com.arges.web.common;

import java.lang.reflect.Field;
import java.util.Collection;
import java.util.List;

import javax.persistence.Column;
import javax.persistence.Table;

import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Restrictions;

import com.arges.web.domain.tableutil.EnhancedStringUtils;

/**
 * SQL进行拼装
 * 
 * 
 * @version Ver 3.0
 * @since Ver 3.0
 * @Date 2012-10-11
 * 
 */
public class SQLPatchUtil
{

	public static final int SQL_MAX_COUNT = 1000;

	public static String creatConditionWhereIn(String field, String conditions)
	{
		return creatConditionWhereIn(field, EnhancedStringUtils.toListForCommarSeperatedStr(conditions, String.class));
	}

	/**
	 * 
	 * 给定字段，拼装出 where XX in （XX,XX）， 对于Conditions中条件大于1000个的，分多次拼装
	 * 
	 * @param field 条件字段
	 * @param Conditions in条件
	 * @since Ver 3.0
	 */
	@SuppressWarnings("unchecked")
	public static String creatConditionWhereIn(String field, List conditions)
	{
		return " where " + createInConditionSql(field, conditions);
	}

	@SuppressWarnings("unchecked")
	public static String createInConditionSql(String dbColName, List values)
	{
		StringBuilder sql = new StringBuilder();
		sql.append(dbColName).append(" in ");

		if (values == null || values.isEmpty())
		{
			sql.append("('') and '1' != '1'");
		}
		else
		{
			int listCount = values.size() / SQL_MAX_COUNT + 1;
			boolean isFirstList = true;
			for (int i = 0; i < listCount; i++)
			{
				int fromIndex = i * SQL_MAX_COUNT;
				int toIndex = (i + 1) * SQL_MAX_COUNT;
				if (toIndex > values.size())
				{
					toIndex = values.size();
				}
				List patialConditions = values.subList(fromIndex, toIndex);
				if (!patialConditions.isEmpty())
				{
					if (isFirstList)
					{
						isFirstList = false;
					}
					else
					{
						sql.append(" or ").append(dbColName).append(" in ");
					}
					sql.append(SQLPatchUtil.formatValue(patialConditions));
				}
			}
		}
		return sql.toString();
	}

	/**
	 * 根据pojoClass实体类生成删除语句，该删除语句不进行级联删除，若有级联表，则需手工删除 values 需删除数据的值 ，以","分隔 field 需删除数据的字段名称 pojoClass 表对应的实体类
	 */
	public static String creatDeleteSqlByFiledWithTableName(String values, String field, String tableName)
	{
		return "delete from " + tableName + SQLPatchUtil.creatConditionWhereIn(field, values);
	}

	/**
	 * 根据pojoClass实体类生成删除语句，该删除语句不进行级联删除，若有级联表，则需手工删除 values 需删除数据的值 ，以","分隔 field 需删除数据的字段名称 pojoClass 表对应的实体类
	 */
	public static String creatDeleteSqlByFiled(String values, String field, Class<?> pojoClass)
	{
		Table table = pojoClass.getAnnotation(Table.class);
		return "delete from " + table.name() + SQLPatchUtil.creatConditionWhereIn(field, values);
	}

	@SuppressWarnings("unchecked")
	public static String getTableColumnNameByPojoFieldName(Class pojo, String name)
	{
		String columnName = "";
		if ("id".equals(name))
		{
			return "id";
		}
		Field field;
		try
		{
			field = pojo.getDeclaredField(name);
			Column column = field.getAnnotation(Column.class);
			columnName = column.name();
		}
		catch (SecurityException e)
		{
		}
		catch (NoSuchFieldException e)
		{
		}
		return columnName;
	}

	public static String getTableColumnNameByPojoField(Field field)
	{
		String columnName = "";
		try
		{
			Column column = field.getAnnotation(Column.class);
			columnName = column.name();
		}
		catch (SecurityException e)
		{
		}
		return columnName;
	}

	public static Criterion getGeneralLikeCondition(String propertyName, List<String> propertyValue)
	{
		Criterion orExpression = null;
		for (String orgCode : propertyValue)
		{
			if (null == orExpression)
			{
				orExpression = Restrictions.like(propertyName, orgCode, MatchMode.START);
			}
			else
			{
				orExpression = Restrictions.or(orExpression, Restrictions.like(propertyName, orgCode, MatchMode.START));
			}
		}
		return orExpression;
	}

	/**
	 * 格式化一行记录，格式成 insert语句中的 column name以及value的格式 如 将 列名 a, b, c 格式化 成 ('a', 'b', 'c') 将 值 a, b, c 格式化成 ('a', 'b',
	 * 'c')
	 */
	public static StringBuilder formatValue(Collection values)
	{
		StringBuilder result = new StringBuilder();
		result.append("(");
		boolean isFirstValue = true;
		for (Object value : values)
		{
			if (isFirstValue)
			{
				isFirstValue = false;
			}
			else
			{
				result.append(",");
			}
			result.append("'").append(value).append("'");
		}
		result.append(")");
		return result;
	}
}
